#sql-server-2005

Answer: What are the implications of the SHOWPLAN permission on SQL Server 2005? #fix #answer #development

What are the implications of the SHOWPLAN permission on SQL Server 2005?

A user wants the SHOWPLAN permission in order to use Execution Plans to tune a query.

What are the implications of this permission. Is it safe to grant a user? I’ve seen the security note here, and that doesn’t concern me with this user.

Are there any other issues to be aware of? From what I see it doesn’t seem like it…

Resolved: MSSQL Large Delete #fix #answer #computers

MSSQL Large Delete

I have a table within a MSSQL database that contains more than 100 Million records spread over about 100 days worth of data. I need to purge some of this data based on its date which is an indexed field in the table. I’ve tried doing a DELETE FROM against an individual date but it was taking a long long time to execute and was causing a drop in server performance. Is there a…

Solution: SQL Server – Cluster vs Mirror for high availability? #computers #development #dev

SQL Server – Cluster vs Mirror for high availability?

I have been doing research into various high availability options for SQL Server 2005. With regard to high availability, what circumstances would favor clustering over mirroring as an option?

From what I can tell, mirroring has numerous advantages including not having the single point of failure storage equipment, no expensive clustering…

Answer: Why "Begin Transaction" before "Insert Query" locks the entire table? #development #answer #dev

Why "Begin Transaction" before "Insert Query" locks the entire table?

I wrote a stored procedure to insert a record. I added “Begin Transaction” just above the Insert Query and execute the query. I noticed that another application that was showing a web page with records from the same table, hanged by the time the insert completed.

Why Begin Transaction locks the entire table? Writers should not…

Fixed: How to search an entire MS SQL 2005 DB for a value? #it #development #solution

How to search an entire MS SQL 2005 DB for a value?

How can I search an entire MS SQL 2005 DB for a value? For example ‘ABC12345′.

Thanks.

Answer [by John Rennie]: How to search an entire MS SQL 2005 DB for a value?

You query INFORMATION_SCHEMA.TABLES for all the tables then query each table.

See http://stackoverflow.com/questions/593746/sql-to-search-the-entire-ms-sql-2000-database-for-a-value

Solution: How do I automatically run nightly backups for Microsoft SQL Server 2005? #answer #dev #computers

How do I automatically run nightly backups for Microsoft SQL Server 2005?

I don’t see any built in mechanism for scheduling nightly backups in SQL Server 2005. What tools are available to perform this task and how reliable are they?

Answer [by Tomalak]: How do I automatically run nightly backups for Microsoft SQL Server 2005?

The SQL Server Agent will do it for you.

There is even a…

How to: Best way to backup a SQL Server database nightly? #it #development #computers

Best way to backup a SQL Server database nightly?

What is the best way to backup a SQL Server 2005 database nightly?

I’m looking for a solution/strategy that would make the database backup file, and place it in an “outgoing” directory. We’re wanting our FTP software to move it out to an offsite server.

Any suggestions on how to make this work as desired?

Answer [by Dave M]: Best way to backup a…

Fixed: Cancel Database Take Offline #fix #development #answer

Cancel Database Take Offline

I accidentally tried to take offline a DB via the context menu command > Tasks > Take Offline.

The process is ongoing now and no new connection can be made.

How do I cancel this process?

Answer [by Adrian Godong]: Cancel Database Take Offline

Found the answer using ALTER DATABASE.

ALTER DATABASE <database_name> SET MULTI_USER WITH ROLLBACK IMMEDIATE

Original post