Here is a dirty little sql that will give you the size of all the databases in your instance :
select DB_NAME(database_id) as DatabaseName, sum((size*8/1024) SizeInMB
from sys.master_files
group by database_id;
This will list all the databases in the instance and add the size of the database file and log file.
One of my DBAs said while trying to restore differential backup he was getting :
Fix : Error : Msg 3117, Level 16, State 4 The log or differential backup cannot be restored because no files are ready to rollforward
and didn’t know what to do. I am not sure if anybody else have encountered this, well I certainly have done the same mistake early in my career. I thought I will blog it so next time I can direct my DBA to this post.
Basically there are three type of recovery state for SQL Server :
Restore with recovery - restore from backup and rollback uncommitted transaction. No further backups or logs can be applied. The database is available for read/write operations after the restore is complete.
Restore with no recovery – restore from backup, but don’t rollback anything. Further logs or backups can be applied. The database is left in restoring mode and no read/write operations can be performed.
Restore with standby - restore from backup, but don’t rollback any transactions, but all the undo information is kept in a file. However the database is available for read-only operations.
In order to restore from a differential backup or restore/apply transaction log, you need to first do a restore with no recovery of full backup, you need to keep restoring with no recovery option until you restore the last file which you will use restore with recovery.
Here is an example of how you would restore a differential backup :
RESTORE DATABASE RAJUDB
FROM DISK = ’D:\MSSQL\BACKUP\FULL\RAJUDB_full.bak’
WITH NORECOVERY;
RESTORE DATABASE RAJUDB
FROM DISK = ’D:\MSSQL\BACKUP\DIFF\RAJUDB_diff.bak’
WITH RECOVERY;
I hope this helps
If you have been working on SQL Server, you would have definitely used sp_who2. One of the limitations of sp_who2 is that you cannot filter the result set directly and we all know how frustrating that can be sometimes. Obviously there are DMVs you can use to get similar information, but if you are like me and like to use the output of sp_who2 to join with other tables and so on , here a way to filter and query the sp_who2. Basically you create a temporary table and insert the output of sp_who2 to the table and Viola ! Here is how :
DECLARE @Table TABLE(
SPID INT,
Status VARCHAR(MAX),
LOGIN VARCHAR(MAX),
HostName VARCHAR(MAX),
BlkBy VARCHAR(MAX),
DBName VARCHAR(MAX),
Command VARCHAR(MAX),
CPUTime INT,
DiskIO INT,
LastBatch VARCHAR(MAX),
ProgramName VARCHAR(MAX),
SPID_1 INT,
REQUESTID INT
);
INSERT INTO @Table EXEC sp_who2;
SELECT *
FROM @Table
WHERE login = ‘raju’
order by DBName;
Now you can join the @table with any other system catalog to get more info or trend patterns.
Have you ever had to search for some keyword in a database object code. Here is a piece of sql that will search through the database (not data) for a keyboard :
select DISTINCT a.name
from sysobjects a (NOLOCK)
join syscomments b (NOLOCK) ON a.id = b.id
where a.type IN (‘P’, ‘TR’, ‘V’, ‘FN’)
and b.text LIKE ‘%cursor%’
This will search through stored procedures, triggers, views and functions defined on the database for the word ‘cursor’.
