Database With Fun (While 1 = 1)

Just try this.  select log_reuse_wait_desc,* from sys.databases where name='dbname' -- see what it is waiting on also run this dbcc sqlperf(logspace) -- see log space utilization   If your recovery model is FULL, follow the below steps 1. Take a full database backup 2. Shrink the log file to an appropriate size 3. Take a full database backup again 4. Schedule transaction log backups to avoid such issues in the future This is a problem for two basic reasons. By default, new databases are created in full recovery mode AND by default, no one has set up log backups on your system. That means it’s up to you. You have to set up log backups. You have to set them and you have to schedule them and you have to ensure they run if you want to recover your database to a point in time, which is also known as, Full Recovery. Though this would have solved the problem, I'm still interested in showing what t-logs are upto actually. What is the Transaction Log? At its simplest,...

you can use the following query hope it helps-   -- We don't need the row count  SET NOCOUNT ON  -- Get size of SQL Server Page in bytes  DECLARE @pg_size INT, @Instancename varchar(50)  SELECT @pg_size = low from master..spt_values where number = 1 and type = 'E'  -- Extract perfmon counters to a temporary table  IF OBJECT_ID('tempdb..#perfmon_counters') is not null DROP TABLE #perfmon_counters  SELECT * INTO #perfmon_counters FROM sys.dm_os_performance_counters  -- Get SQL Server instance name  SELECT @Instancename = LEFT([object_name], (CHARINDEX(':',[object_name]))) FROM #perfmon_counters WHERE counter_name = 'Buffer cache hit ratio'  -- Print Memory usage details  PRINT '----------------------------------------------------------------------------------------------------'  PRINT 'Memory usage details for SQL Server instance ' + @@SERVERNAME + ' (' + CAST(SERVERPROPERTY('productversion') AS VARCHAR) + ' -...

Parallel Processing Using Jobs We'll create the 'job queue' and fill it with sample 'waitfor delay' commands with variable wait time using rand() function. The jobs will just simulate the work. create table #job_queue ( id int identity ( 1 , 1 ) primary key , exe_proc varchar ( 255 )) declare @create_job nvarchar ( max ), @db_name sysname , @job_name varchar ( 255 ) declare @val int , @command nvarchar ( max ), @delay varchar ( 16 ), @i int set @db_name = db_name () set @i = 1 while @i <= 100     begin         insert #job_queue values ( 'waitfor delay ''''0:0:' + cast ( ceiling ( rand () *10 ) as varchar ( 3 ))+ '''''' )         set @i = @i+1     end 2. Starting the main loop. Create the SQL for the job first: while exists ( select 1 from #job_queue ) begin   select top 1 @val = id , @comman...

Due to sudden sheer interest in VMware and windows administration, some of the main topics were jotted down. Future blogs will cover each of them.

For each file to be moved, run the following statement. ALTER DATABASE database_name MODIFY FILE ( NAME = logical_name , FILENAME = 'new_path\os_file_name' ); Stop the instance of SQL Server or shut down the system to perform maintenance. Move the file or files to the new location. Restart the instance of SQL Server or the server. Verify the file change by running the following query. SELECT name, physical_name AS CurrentLocation, state_desc FROM sys.master_files WHERE database_id = DB_ID(N'<database_name>'); If a file must be moved because of a hardware failure, follow these steps to relocate the file to a new location. This procedure applies to all system databases except the master and Resource databases. If the database cannot be started, that is it is in suspect mode or in an unrecovered state, only members of the sysadmin fixed role can move the file. Stop the instance of SQL Server if it is started. Start the ...