Transaction Logs

Transaction log full:

If you reach 100% of the log size and 100% of the disk size, you'll need to clear it and shrink it using DBCC SHRINKFILE

If you reach 100% of the log size but not 100% of the disk size, and Database is in simple mode, you either need to do a database checkpoint (CHECHPOINT) or increase log file size (though this is a temporary solution)

If you reach 100% of the log size but not 100% of the disk size, and Database is in Full/Bulk Logged recovery model, you (most likely) need to take a log backup (and do it regularly).

List all columns in all table in a database XML PATH

SELECT db_name() as databaseName, table_name=sysobjects.name,
(Select syscolumns.name + ',' FROM
syscolumns where sysobjects.id = syscolumns.id
order by syscolumns.name FOR XML PATH('')) as columnlist
FROM sysobjects
WHERE sysobjects.xtype='U'
ORDER BY sysobjects.name

Passing variable to .sql from a bat file

sqlcmd -S %1 -i select.sql -v dbname = %2 tablename = %3 -o %4

select.sql
use $(dbname)
select * from $(tablename)

or

then you can also do in select.sql
DECLARE @dbname1 as varchar(150)
SET @dbname1 = '$(dbname)'
in sql and use it across the script to create dynamic sql

Dynamically passing variables to .sql through bat file

runscript.bat
REM echo %2 >> log\all.log
sqlcmd -S -d -E -i upgrade.sql -olog\upgrade%2.log -v servername=%1 sitecode=%2
type log\upgrade%2.log >> log\all.log

:END

upgrade.sql:
DECLARE @vsql NVARCHAR(4000)

SET @vsql = ''
Declare @serverName NVARCHAR(255)
Declare @sitecode NVARCHAR(50)
set @serverName = '$(servername)'
set @sitecode = '$(sitecode)'
Print @serverName
Print @sitecode


SET @vsql = 'SET IDENTITY_INSERT dbo.[] ON
insert into dbo.[GL_Site_SourceTests]
(,)
select distinct ,
from ['+@serverName +'].'+ @sitecode +'_dmss.dbo.[] i
where not exists (select 1 from dbo.[] s
where i.site_tk = s.site_tk
and i.gl_site_sourceTest_tk = s.gl_site_sourceTest_tk)
SET IDENTITY_INSERT dbo.[] OFF'


--Print @vsql
EXEC sp_executesql @vsql

Drop all contraints on a table

-- t-sql scriptlet to drop all constraints on a table
SELECT
'ALTER TABLE ' + object_name(parent_obj) + ' DROP CONSTRAINT ' + [name]
AS Script into #temp
from sysobjects where --xtype IN ('F','TR')
object_name(parent_obj) like '%%'
and (name not in (',')
and name not like '%%')
--drop table #temp
Select * from #temp

DECLARE get_sqlstmt CURSOR FOR
select Script
FROM #temp

OPEN get_sqlstmt
FETCH NEXT FROM get_sqlstmt
INTO @vsql
WHILE @@FETCH_STATUS = 0
BEGIN
Print (@vsql)
EXEC(@vsql)

FETCH NEXT FROM get_sqlstmt INTO @vsql
END

CLOSE get_sqlstmt
DEALLOCATE get_sqlstmt

Drop table #temp

Asp.net and SQL privileges

When a .net app stops working and it has connections to database.

eg: If the executeReader works and executenonQuery fails, it means either the user does not have write privilages or the process runs
under an identity (usually ASPNET) that only has read access
to the database. For this Right click
on the database file in Windows Explorer, go to the
Security tab, and give the ASPNET user write and modify rights to the file.

Generate Insert statement for multiple tables dynamically

DROP PROC InsertGenerator
go
CREATE PROC InsertGenerator
(@tableName varchar(100)) as

--Declare a cursor to retrieve column specific information for the specified table
DECLARE cursCol CURSOR FAST_FORWARD FOR
SELECT column_name,data_type,table_schema FROM information_schema.columns WHERE table_name = @tableName
OPEN cursCol
DECLARE @string nvarchar(3000) --for storing the first half of INSERT statement
DECLARE @stringData nvarchar(3000) --for storing the data (VALUES) related statement
DECLARE @dataType nvarchar(1000) --data types returned for respective columns
DECLARE @schemaType nvarchar(100) --table schema value for correct schema execution
SET @stringData=''
DECLARE @Query nvarchar(4000)


DECLARE @colName nvarchar(50)

FETCH NEXT FROM cursCol INTO @colName,@dataType,@schemaType

IF @@fetch_status<>0
begin
print 'Table '+@tableName+' not found, processing skipped.'
close curscol
deallocate curscol
return
END

WHILE @@FETCH_STATUS=0
BEGIN
SET @stringData=@stringData + @colName+','
--SET @string=@string+@colName+','

FETCH NEXT FROM cursCol INTO @colName,@dataType,@schemaType
END
set @stringData = substring(@stringData,0,len(@stringData))
SET @string='INSERT INTO '+@schemaType+'.'+@tableName+'(' +@stringData +')'
SET @query =@string +'SELECT '+@stringData+' FROM '+@schemaType+'.'+@tableName
Print @query
--exec sp_executesql @query

--select @query

CLOSE cursCol
DEALLOCATE cursCol


GO

Run: InsertGenerator @tablename