Max capacity specifications SQL2008

Clustered Index:1
Non clusetered index: 999
xml index: 249
Fk's: 253

columns per FK, PK, index: 16
Databases, Filegroups, Files per instance: 32,767
Max DB size: 524272 TB
Instances per computer: 50 on satnd alone, 25 on failover
Max number of objects in a DB(views, procs, index,keys, triggers): 2,147,483,647 (Triggers are limited only by max no of objects)

For more details: http://msdn.microsoft.com/en-us/library/ms143432.aspx

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.