Beyond Web Logs

Yes many times, when restoring database we get this error, this is due to "restore a database on another database which are not the same database. For example, you have backup Northwind database and try to restore the backup to AdventureWorks database, the error will occur." How to solve this ?   Simple solution: On Restore D... [More]

SQL Server

Sometimes SQL Server Service sqlservr.exe is cause system slowness due to high memory usage, same issue was with me, while checking this issue, i come to know that we need to set maximum memory usage in SQL server, after setting this system is working fine, but you may need to set memory size according to your database needs. SQL Server does not d... [More]

SQL Server

Note SQL Server 2005 Enterprise Edition is not supported on Windows Vista. you need to install any of the following flavors: SQL Server 2005 Developer Edition SQL Server 2005 Standard Edition SQL Server 2005 Workgroup Edition SQL Server 2005 Evaluation Edition SQL Server 2005 Express Edition with Advanced Services Service Pack 2 (SP2)... [More]

SQL Server, How To

SQL Server

With this script you will get all databases backups (seprate .bak files with date), you will need to add this script in SQL Server Management Studio then expand SQL agent Then right click on Jobs. Click New Job... Give name in Name area and click Steps Then Click New and paste following script there. Click Ok. From Schedules set it fo... [More]

SQL Server connection string, web.config

Problem:If you are getting the following error:"Generating user instances in SQL Server is disabled. Use sp_configure 'user instances enabled' to generate user instances" What should I do? Answer: To fix this, please 1. Open the SQL Server Management Studio Express.2. In the query editor type this text: exec sp_configure 'user instances... [More]

SQL Server

In Microsoft SQL Server 2008 the Standard, Enterprise, Developer and Web editions can use whatever the Operating Systems maximum allowed memory is. Windows Server 2003 Windows Server 2003 Standard Edition can use a maximum of 4 GB. Microsoft Windows Server 2003 Enterprise Edition can use a maximum of 32 GB for 32-bit (x86) machines and 64 GB for ... [More]

SQL Server

eXtensible Markup Language (XML) has been widely adopted as a platform-independent format for data representation. It is useful for exchanging information among loosely coupled, disparate systems, such as in business-to-business (B2B) applications and workflow situations. Data interchange has been a major driver of XML technologies. XML is increasi... [More]

SQL Server

Normally you could use following to reduce SQL Server log file size: USE DatabaseName GO             DBCC SHRINKFILE(<TransactionLogName>, 1)     BACKUP LOG <DatabaseName> WITH TRUNCATE_ONLY    &nbs... [More]

SQL Server

View Triggers in SQL Server:   To list triggers in SQL server database, you can use following simple query:   select * from sys.triggers   Alternatively, use the system stored procedure sp_helptrigger as well, to list triggers associated with the table:   Exec sp_helptrigger 'dbo... [More]

SQL Server

Here is the simple query you can use to fetch column information from a given table: SELECT ORDINAL_POSITION ,COLUMN_NAME ,DATA_TYPE ,CHARACTER_MAXIMUM_LENGTH ,IS_NULLABLE ,COLUMN_DEFAULT FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'Product' -- place your table in question here in quotes ORDER BY... [More]

SQL Server, Tips n Tricks

Proper indexing in SQL Server is extremely important for application performance. Here are some common design guidelines for choosing the right index setup: A clustered index is used on fields that increase continuously, e.g., auto number integer fields. Because SQL Server physically arranges rows in the database file based on a clustered ind... [More]

SQL Server indexing

Gregg Stark has created a very nice query that is a great tool for optimizing indexes (indices) in SQL Server 2005.  Here's the query: SELECT o.name AS object_name, i.name AS index_name , i.type_desc, u.user_seeks, u.user_scans, u.user_lookups , u.user_updates, u.last_user_seek, u.last_user_scan , &#... [More]

SQL Server index, performance

If your database's log file size reaches its limit, you can truncate and then shrink it to its minimum size by using the following commands: USE DatabaseName GO DBCC SHRINKFILE(<TransactionLogName>, 1) BACKUP LOG <DatabaseName> WITH TRUNCATE_ONLY DBCC SHRINKFILE(<TransactionLogName>, 1) If you'v... [More]

How To, SQL Server

If your SQL Server 2005 is not starting because of modellog.ldf file as it got corrupted or something, all you have to do is replace modellog.ldf AND model.mdf files, and your SQL Server will be on its way. You can copy these two files from another location and paste them in the directory of your SQL Server. Hope it helps.

How To, SQL Server