use <DatabaseName>select * from information_schema.tables Example: use myDatabaseselect * from information_schema.tablesorder by table_name
Category: SQL
Shrink (truncate) Microsoft SQL transaction log files
Shrink (truncate / purge) MS SQL transaction log files. Use <dbname>Goalter database <dbname> set recovery simplegodbcc shrinkfile (<dbname>_log, 100)gocheckpointgodbcc shrinkfile (<dbname>_log, 100)alter database <dbname> set recovery fullgo Example: Use myDatabaseGoalter database myDatabase set recovery simplegodbcc shrinkfile (myDatabase_log, 100)gocheckpointgodbcc shrinkfile (myDatabase_log, 100)alter database myDatabase set recovery fullgo
View logical log filename and physical location for MS SQL database files
View logical log filename and physical location (current DB only).select physical_name from sys.database_files where type = 1 View logical log filename for all DBs: select a.name, b.name as ‘Logical filename’, b.filename from sys.sysdatabases ainner join sys.sysaltfiles bon a.dbid = b.dbid where fileid = 2
View SQL DB log size and space used
View DB log size and space used. use dbcc sqlperf(logspace) Example: use myDatabase dbcc sqlperf(logspace) MSSQL
Command line reference: Database and Operating Systems.
Great resource for Windows and Linux command line utilities as well as SQL, Oracle and Windows PowerShell. http://ss64.com/index.html
MSSQL: View actively running commands by SPID
— View actively running commands by SPID. select session_id, Textfrom sys.dm_exec_requests rcross apply sys.dm_exec_sql_text(sql_handle) t TroubleshootingBackup failed: System.Data.SqlClient.SqlError: Backup and file manipulation… must be serialized