New answers tagged

1

I'm assuming you are backing up to the same file each time. There is an option to INIT or NOINIT when you run a backup. NOINIT (which is the default) tells it to just add an additional backup to the file. Per BOL: { NOINIT | INIT } Controls whether the backup operation appends to or overwrites the existing backup sets on the backup media. The ...


2

The backup file contains multiple copies of the database since it is not being "initialized" or "formatted" with each backup. Go to the 'Options' tabl on the "Back Up Database" dialog box, and select the "Overwrite all existing backup sets":


0

In order to create auto numbers without gaps we can use QUEUE object of MS SQL Server. So the idea is that Queue supports concurrency and roll back of transactions. We use SEQUENCE object to generate continuous set of numbers and send them to QUEUE. After that we can receive them back from that Queue concurrently and if one of client's requests fails then ...


0

You need specific permissions to use OPENQUERY().


1

So, after lots of hard thinking and trial and error (mostly error I must admit), it seems I stumbled upon an answer to my question. I did the following: create a temp table will hold the result, let's call it result. fill result with the packaged data. update the temp table with the produced order. The temp table: CREATE TABLE ...


1

There are couple of options -- I would not suggest to go with replication as you are making schema changes and it will require to reinitialize the whole replication with a snapshot. Logshipping with standby option: Cheap and effective. Works great and less overhead of maintenance. Things to look out : If an adhoc log backup is taken, it will break ...


6

Putting in a simple language : A login gives you access to server. A user gives you access to database. A login must map a user so that you can access the database. For SQL Authentication, you should create the login with SAME SID and map it to your user database/s on all the nodes with proper permissions (follow the principle of least privileged). This ...


0

Usage statistics come from sys.dm_db_index_usage_stats, which tracks the number of execution plans that include an operator touching that index. It's reset on SQL Server service restart, or when the index is modified. Operational statistics come from sys.dm_db_index_operational_stats, which track the number of times the index has actually been touched. It's ...


0

I would recommend backing up the transaction log more frequently than once a day, possibly every 15 minutes. It may be that a single very large transaction has blown up its size. You can monitor how full the transaction log gets during the day (in between log backups) using the command dbcc sqlperf(logspace) And yes, you can shrink the log, using a ...


2

After much frustration and fruitless googling, I finally figured out the problem. It was not that the accounts for the 'SQL Server' and 'SQL Server Agent' services did not have access to the directory. The problem was that the account for the 'Log Reader Agent' did not have access. Should have been obvious from the error message, but I just didn't get it... ...


2

No, dropping a synonym requires a Sch-M lock, and also IX locks on sysschobjs/sysobjvalues, at least if it is on the same server (I haven't tested what happens when the synonym uses a 4-part name). What exactly would it mean if you could change a synonym in the middle of a query or transaction? If you have a query plan operator or a cursor which needs to ...


1

You don't state the steps you take in each case so it is difficult to give specific analysis but three generic explanations for some of the difference spring to mind: Rebuilding is an offline operation unless you are using Enterprise Edition, so it takes a table lock and does all the work before letting any other processes touch it. This means the process ...


1

For the purpose of this answer, I am assuming your INSERT statement looks something like this: INSERT INTO dbo.vSubCategories (CategoryName, SubCategory) SELECT CategoryName, SubCategory FROM ... /* reading rows either from the CSV directly or from a staging table into which the CSV has been imported */ ; So, the first thing your ...


2

The Memory Use column - where is it coming? It corresponds to memory_usage column in sys.dm_exec_sessions. As a friendly note, I would suggest not to use Activity Monitor. Use Adam Machanic‘s free procedure, sp_WhoIsActive (uses DMVs). You can even log into a table for post analysis. Kendra Little talks about Activity monitor limitations : It ...


2

I had similar error few months ago and solved by this. USE master; GO EXEC master.dbo.sp_delete_log_shipping_primary_secondary @primary_database = N'<PrimaryDatabase>' ,@secondary_server = N'<SecondaryServer>' ,@secondary_database = N'<SecondaryDatabase>' GO


2

After posting this question and getting some comments about upgrading SQL Server to the latest SP, I found this KB article. The behavior described in the article seemed to be very similar to what we were seeing. We went ahead and applied SP3 + CU3 to get to the most recent version of SQL 2012 that we could. After installing the SP and CU, the problem ...


0

It appears that the dates you are clustered on are not being inserted sequentially. If your date field was recent purchases for example they would be inserted at the 'end of the table'. Because it is taking so long to do the insert it seems that SQL is having to reorder your data with each insert instead of tacking it on the end. Determine if your ...


5

A good option for you, I would imagine, is to split table logically, so every section would be in its own folder and have under 300k files(=records). In SQL Server logical table split is usually done with Partitioning (this is an Enterprise feature). Partitioning essentially maps sections of table (partitions) to physical storage (filegroups). Various ...


1

You just installed SQLManagementStudio_x64_ENU this is SQL Server management studio. This is a tool used to connect to SQL server database engine. Since you have not installed SQL Server database engine you cannot connect. You have to browse This Link Click on download Then you would get something like below Downlaod the files marked in rectangle. ...


0

Were you able to get this sorted out? If not , try the following: Go to the Failover Cluster Manager ( cluadmin.msc from Run). Expand the Cluster name ( Expand meaning click on the + sign ). Expand Services and Applications. You should now see your Availability Group listed there. R-click on that and go to Properties. In the General Tab you should see ...


0

Since you recently upgraded a few months ago from SQL Server 2005 EE to 2012 SE, you should save off and clear your event logs because SqlAgent may crash when it sees Event Log IDs from an older build of SQL Server. For example, the Agent may crash because it reads an event logged by a SQL Server build earlier than the current build, which only contains data ...


3

TailLog backup contains the transaction log that was generated after the last log backup.so this is necessary if you want to restore to latest point in time.When we try to restore the database which is online and whose tail log has not been backed up ,SQL Server will throw the error you are getting To avoid this error use NoRecovery. But with above ...


2

The server has too little memory. 16GB is the amount of memory found in a high end tablet. That said, this may be secondary because varbinary aside, the database is quite small. But even that is overloading the server memory. This is quite important because of... For most database use, disk speed is paramount, CPU does not matter (because memory as cache ...


7

This table should have a clustered index on UserId. If there is no more than one thumbnail per user, then the clustered index can be the primary key. Since you say you have a currently-running system on SQLServer 2012 standard edition, online index builds are not possible. You could wait for a low-activity period to create the index, or create a new table, ...


1

I agree with Chad Mattox's answer. But to simplify the process, I'd recommend you to use the following t-sql to do the backup BACKUP DATABASE LiveDB TO DISK = '[tempfile]' WITH COPY_ONLY, INIT; By adding INIT in your backup script, you will always overwrite the existing one and thus simplify your restore script (by no need to find the latest File [X])


4

The module text is encrypted using an RC4 stream cipher. The RC4 initialization key is computed from the SHA-1 hash of: The database family GUID (from sys.database_recovery_status) Converted from uniqueidentifier to binary(16) The module's object ID (from the catalog views) Converted from integer to little-endian binary(4) The module's object sub-ID ...


10

The biggest difficulty in coming to a precise solution is in defining exactly what characters are to be included (or excluded, whichever direction makes more sense for the operation). Meaning: Are we talking about VARCHAR / ASCII data or NVARCHAR / Unicode data? The list of punctuation characters for ASCII data depends on the Code Page which in turn ...


5

I may be over-simplifying this a bit but, if we say that punctuation is all that is left when alphanumeric values are removed, then the following will search for strings that have non-alphanumeric characters in them. Create Table #Test ( Value VarChar(10) ) Insert Into #Test Values ('123a'), ('456b'), ('12ABC'),('AB!23'),('C?D789') -- Original Select ...


8

You're using a local variable, not a parameter. So because this is part of your query text: DECLARE @id INT=43865 It gets included as part of the query hash (which converts the query text, including 43865, to binary), and thus produces a different hash every time you change the text. This is how it will work in a Management Studio query window, but it ...


4

Yes, the design looks great. Minor notes: You could use TINYINT, instead of the INT for the ClassTypeID. Or even CHAR(1) and have 'A' and 'B' instead of 1 and 2. 1 byte instead of 4 means you are saving 3 bytes in every row, in all 3 tables and in every index that includes ClassTypeID - which would be every index on these tables, if ClassTypeID is part of ...


2

I suspected multiple backups might exist in this backup file. If you run: RESTORE HEADERONLY from disk = [tempfile] this will show all the backups that are in the file. You are not restoring the latest backup, use FILE=X to restore the appropriate backup.


1

To research this, I configured transactional replication on my test server with 1 article having row filter. I did this for Adventureworks.Sales.Store table having [CustomerID] <10. Initially, I tried querying distribution..sysarticles to check the filter details. But, surprisingly it did not show any data in it. Then I checked ...


2

This sounds like you might have a long running transaction on the primary replica. Run this query to find any transactions that have been running longer than 10 mins: Select B.session_id, A.transaction_id, C.transaction_begin_time, DATEDIFF(Second,C.transaction_begin_time,getdate()) TimeTaken_In_Seconds, B.HOST_NAME, B.program_name, ...


4

You can use the following Stored Procedure, scheduled via a SQL Server Agent job, to archive records on a continual basis. You don't need to worry about the initial move of "everything older than 6 months" as it will be taken care of naturally by this process since it will be able to archive records faster than you are inserting them. Of course, if you want ...


2

Two approaches that I have seen used. Approach 1 Create an identical table to audlog, say audlog_new. Adjust Identity seed if necessary to be greater than the largest ID on your current audlog table. Rename your existing table to something else (audlog_old) and then rename audlog_new to audlog. Now you have all the time in the word to process your old ...


3

Can anyone shed some light on this behavior? The behavior you're describing is not working as described. The end result is the same but the inner workings are different than what you're thinking. The Availability Group for this specific DB is synchronized and healthy. I had to add a second transaction log file (logfile2) to the primary. The ...


4

You should only require a server reboot if something is locked, correct. However, during the patching, the SQL service will be stopped, patched then restarted so you'll actually be bouncing the service itself twice. Once for the SP, then again for the CU.


2

To see open transactions which may be locking rows, execute: SELECT * FROM sys.sysprocesses WHERE open_tran = 1 Issuing a COMMIT or ROLLBACK for those transactions would address your issue. If you don't mind dirty reads (depends on your business rule for your particular situation) you can SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED And this ...


0

You could try querying various dmvs with the following script. SELECT TEXT, request_time, grant_time, query_cost, timeout_sec, wait_order, wait_time_ms, group_id, requested_memory_kb, granted_memory_kb, required_memory_kb, used_memory_kb, max_used_memory_kb, ideal_memory_kb, query_plan, 'all information >', ...


2

Removing permissions is not generally going to work because you can't be CERTAIN that someone doesn't have permissions. Possibly through a group, role or even because they are sysadmin (although let's hope not). For tables you can disable them. And that is a quick process. However to enable them requires you to rebuild them and for a large table that ...


6

Is there any other way to achieve the same(tables cannot be used anymore) without dropping them? A schema change is a very fast operation - just metadata change is required. The original idea I got was from Aaron Bertrand's blog - Schema Switch-A-Roo. You can follow the steps from my answer here Obviously there are other methods like sp_rename N'old ...


2

Remove the permissions as Phil W. suggests. Also remove the permissions from any stored procedures that use the tables. In SQL Server, (I don't know about others) permissions are chained from a calling object (e.g. the sproc) to the called object (e.g. a table).


5

Remove the permissions on the table from the Role(s)/ Group(s)/ Account(s) that [might] be using it. If anything blows up, put them back [quickly]. Hint: Using a script to do make these changes would be a really, really Good Idea.


11

A couple of other options are to just rename the tables, or if they have clustered indexes, you can disable the clustered index.


4

Use LEAD() to get the next row within the TicketId partition. Then join to a Calendar table to get all the dates between. WITH TAwithnext AS (SELECT *, LEAD(AssignmentDate) OVER (PARTITION BY TicketID ORDER BY AssignmentDate) AS NextAssignmentDate FROM TicketAssignment ) SELECT t.TicketID, c.Date, t.DepartmentID FROM dbo.Calendar c JOIN TAwithnext t ON ...


2

This is a quick way of doing (I have not tested for performance or scalablity) -- create Calendar table -- borrowed from @Aaron's post http://sqlperformance.com/2013/01/t-sql-queries/generate-a-set-3 CREATE TABLE dbo.Calendar(d DATE PRIMARY KEY); INSERT dbo.Calendar(d) SELECT TOP (365) DATEADD(DAY, ROW_NUMBER() OVER (ORDER BY number)-1, '20160101') ...


5

Obviously at this point you've realized that zip codes should be stored as strings not numbers. If for no other reason than the fact that foreign (non-US) zip codes frequently have letters in them. That said let's go back to your original question. Why was your length showing 5 when you had 5 digits and 12 when you were only storing 9 digits. That has to ...


8

Zip codes are strings, not numbers. Some of them have 1 or even 2 (but not more than 2) leading zeroes. The datatype in the import table should be VARCHAR(10) so that it can hold 5 digit and 9 digit + hyphen zip codes. Even if you never have to store postal codes of other countries, and even if the values will only ever have numeric digits (i.e. 0 - 9), this ...


1

I had an issue where an old server needed to read data from a new server for backwards compatibility. The old server cannot be upgraded as it contains a legacy system that is being deprecated, but still used. I needed SQL Server 2012, downloaded it and it refused to run on Server 2003 SP2. I used Orca and removed the installrequirement, and it installed ...



Top 50 recent answers are included