SQLGear

Recently trying to restore a database following message was thrown: System.Data.SqlClient.SqlError: File 'F:\DATA\DatabaseName.mdf' is claimed by 'DBFileGroup1'(3) and 'DBFile'(1). The WITH MOVE clause can be used to relocate one or more files. (Microsoft.SqlServer.Smo) The issue might be because of the restore process using same physical file name and path for two different database files. Go to the options tab and see. Following is the screenshot: In any file system there would be no physical files with same name in any given folder. This is why the error comes. Just modify the file name as per your requirement as below: Now hit the OK button. This will restore the database without any error.

Credits unicellular If you are taking backup of a database through wizard or through t-sql script you can get the progress or percent of process completed. This helps when you wait for a backup job to finish before doing your changes to the database. Mostly you plan your changes in prod just after the backup job finishes, and this is obvious option if your database is too big. But the backup job for a database executed by a schedule does not show the progress of it. At this moment you wished how much more you have to wait. Well sys.dm_exec_requests is the DMV for that if you know the session id of the job. SELECT percent_complete FROM sys.dm_exec_requests WHERE session_id = 64 Following script will give you the minutes remaining for the process to be completed. SELECT percent_complete,DateDiff(mi, start_time, GETDATE()) As MinutesTillNow, ((100-percent_complete)*(DateDiff(mi, start_time, GETDATE())))/percent_complete As [MinutesRemaining] FROM sys.dm_exec_requests WHERE session...

Recently one of the developers sent me following error: Message: SQL Error [Microsoft OLE DB Provider for SQL Server: There is insufficient memory available in the buffer pool. SQL State: 42000    Native Error: 802 State: 20     Severity: 17 SQL Server Message: There is insufficient memory available in the buffer pool. I checked the memory usage by components under Memory Consumption server report. Following is the screenshot of the report: I found that CACHESTORE_SQLCP was allocated most memory. This type of memory clerk indicates memory consumption by plans of SQL statements or batches not found in any stored procedures, triggers or functions. I just wanted to clear pool for CACHESTORE_SQLCP. The name for this memory clerk is "SQL Plans". Which I found by querying sys.dm_os_memory_clerks dynamic management view. I then executed the following command to clear the pool for CACHESTORE_SQLCP. DBCC FREESYSTEMCACHE('SQL Plans') The memory a...

Sometimes you may need to find number of rows in each (or few) tables. Following query will determine the number of rows in each partition of each table in each schema.