Jesoba.com is launched, Click below image to access it

Jesoba.com is launched, Click below image to access it
Technology and Entertainment Blog

SQL server queries list

SQL server queries list

SQL server queries or commands list. As a DBA, every database administrator should know at least some most common SQL queries and should have them handy whenever required.

I am posting these queries here so that you don't have find them here and their.

10 SQL server queries list I have added below, will add more queries soon, keep visiting.

1. SQL Query to find database location

SELECT DB_NAME(DATABASE_ID) as DATABASE_NAME,name as [FILE_NAME],physical_name as [PATH] from sys.master_files
----------------------------------------------------------------------------------------------------------------

2. SQL query to find correlation id


SELECT * FROM dbo.RequestUsage WHERE CorrelationId ='01a1dc05-7382-43c9-a74a-9f9561b63e09'
----------------------------------------------------------------------------------------------------------------

3. Command to check database size and database location, total size.

sp_helpdb dbname
----------------------------------------------------------------------------------------------------------------

4. Command to set database in multiuser mode

ALTER DATABASE [SharePoint_Config_29c26fca-17b8-48c1-9704-b869932abcb6] SET Multi_User
----------------------------------------------------------------------------------------------------------------

5. SQL query to know job status, whether job is hang or in progress and how much time more it will get tak, how many % it is completed.

SELECT A.NAME,B.TOTAL_ELAPSED_TIME/60000 AS [Running Time],
B.ESTIMATED_COMPLETION_TIME/60000 AS [Remaining],
B.PERCENT_COMPLETE as [%],(SELECT TEXT FROM sys.dm_exec_sql_text(B.SQL_HANDLE))AS COMMAND FROM
MASTER..SYSDATABASES A, sys.dm_exec_requests B
WHERE A.DBID=B.DATABASE_ID AND B.COMMAND LIKE '%BACKUP%'
order by percent_complete desc,B.TOTAL_ELAPSED_TIME/60000 desc
----------------------------------------------------------------------------------------------------------------

6. Query to check for any database issue, consistency, allocation error.

dbcc checkdb
---------------------------------------------------------------------------------------------------------------------------

7. Know when the last time CHECKDB was run.


dbcc checkdb history

dbcc dbinfo ('Your_Database_Name')WITH TABLERESULTS
---------------------------------------------------------------------------------------------------------------------------

8. SQL query to backup and shrink (Truncate) log files.


backup log [Your_Database_Name] to disk='E:\sql_backup\Your_Database_Name_BackupTime.trn'

And click database, log file and shrink it.

If even after clikcing shrink the LDF size does not reduce then re-run above command with adding time value to TRN and then again click shrink, re-do it until LDF size reduces.

E:\sql_backup\Your_Database_Name_BackupTime_NewTime.trn
---------------------------------------------------------------------------------------------------------------------------

9. Query to set database in multiuser mode

ALTER DATABASE [Your_Database_Name] SET Multi_User
---------------------------------------------------------------------------------------------------------------------------

10. Query to attach database through SQL query.

CREATE DATABASE [SharePoint_Config_6d3394d6-f3fb-40fd-9723-8cc57188c99b] ON
           ( FILENAME = N'D:\sharepoint data\Your_Database_Name.mdf'),
           ( FILENAME = N'D:\sharepoint data\Your_Database_Name_log.ldf')
            FOR ATTACH ;
           
Note : Replace database name.
-----------------------------------------------------------------------------------------------

If you have other SQL queries and want to add them here, please leave us comment with SQL queries you want to add.

No comments:

Google+ Badge

About Author

My photo

I am Senior Server Expert and Professional Blogger.