Transaction logs growing out of control?

login to SSMS and click new query :
backup log adventureworks with truncate_only

next, right click database adventureworks>tasks> shrink> files>file type > logs> click ok
thats it --only ms SQL server 2005

MS SQL 2008 R2 useful features

Yes ,SQL server 2008 has a new version again. Its R2 folks. Im going to give you some basic and useful ways to use the new features

• Policy based management (PBM) --control database,table, trigger and object names
• Resource governer --take control of your memory and processor again
• Back up compression ---Dont need more SAN LUNS
• data file encrytion --- someone stole your mdf files..no worries
• filestream--- replace BLOB with this
• New report builder 3.0 --more charts and datasets added
• New database mirroring recovery options
• totally rebuild database replication engine
• New sharepoint integration..

How to change the default port in MS SQL server ---2005 2008

By default MS SQL server 2005 uses dynamic ports. You have to change the port to static if needed.

Go to SQL server configuration
>Protocols for MSSQL
>click TCPIP
>IP Address
>remove dynamic port
>TCP port
change to your specific port

Am I seeing double --Find duplicates in a sql table

Find duplicates in your table.
This is an example for a database called instructapp and table called teacherInfo and a column called SSN

USE instructapp
SELECT SSN, COUNT(*) TotalCount
FROM teacherInfo
GROUP BY SSN
HAVING COUNT(*) > 1
ORDER BY COUNT(*) DESC

Upgrade service pack on a MS SQL 2000 cluster

Steps to upgrading a MS SQL 2000 cluster is backing up the system databases
--backup database master to disk = ""

1. download the service pack from Microsoft.
2. Run the service pack against the active node ( do not attempt to run on passive node)
   
3. find the password for your cluster admin service ( your going to need it)
   

Take advantage of over 4 GB's memory in MS Sql server 2000

So you are having performance issues in MS SQL 2000? SQL server is sluggish and you have 6 GB's or more of memory.What could be the problem? Your tables all have indexes and databases are not set to auto shrink.

This sounds like you need AWE enabled. AWE
Launch Enterprise Administrator and run. this example is for 6 GB's

sp_configure 'show advanced options', 1
RECONFIGURE
GO
sp_configure 'awe enabled', 1
RECONFIGURE
GO
sp_configure 'max server memory', 6144
RECONFIGURE
GO

this command works for 32 bit only it does not work for 64 bit

Recovering from corrupt master (system) in MS SQL 2000!!!

SQL server 2000 wont start? This is not for SQL 2005. Check the logs and there is a corrupt master database right?
Well, get ready for the fun because it all starts here.

• You will need a utility called rebuildm.exe ( its usually in your ms SQL 2000 disk)
• This will install a new master, model, tempdb, msdb
• start the SQL service in single user mode
• Launch Enterprise Admin in single user mode to recover master database.
  
• All the databases, tables, views, users and objects should be back in place
  

Learn to kill blocks in MS SQL Server 2000

Hey DBA's let stop thinking that tables are corrupt. Let start to kill the blocks or locks associated with that table
How to:
Open Enterprise Administrator>
Local instance>
Management>
Current activity>
Locks/Process ID>
Find the process>
kill process>
Done.