I want to control the size of ldf files and mdf files of several databases on SQL Server 2008 in my organization (manual increase), but i have a question:
What would be the best practices (best methods) for provisioning a ldf file and mdf file? Exists any generic formula?
With this i want to avoid the shrink operation and the autogrow of sql server databases...
We have a vendor created database with 9000+ tables, one of which has about 6 billion rows. The vendor redesigned the database recently and ever since we've had terrible performance.
What the vendor did was increase any and all varchar columns (tens of thousands of columns) to 256.
Before the upgrade we had no problems creating an index on the 6billion row table, it would take 2 hours.
Now after the upgrade we've let the index creation command run for 5 days and killed it because it was consuming terabytes of logspace.
The previous design had combined column width of 1049 to what is now over 4000. The primary key itself is 1283 characters (SQL limit is 900).
There is no additional data, just wider columns. Why we are unable to create the index?
What is happening inside SQL Server? Does SQL make "room" in memory for the index for the entire width of the potential max row length?
Have a SQL2008R2 instance on a VM where the single .mdf for the tempDb database is located on a high contention disk. I've managed to get another 60GB disk and thought it would be a good time to move the .mdf and also increase it's size and number of files.
The server has 12 cores and after a bit of reading I've decided that it would be best just to have four files for this database as the 1 file per core (-1) seems to be disputed.
-- Move the existing file to the new disk and rename it.
ALTER DATABASE tempdb MODIFY FILE (NAME='tempdev', FILENAME='E:SQLData empdb0.mdf');
-- Change the size to 1GB
ALTER DATABASE tempdb MODIFY FILE (NAME='tempdev', SIZE= 1048576KB, FILEGROWTH=5%);
-- Add three new files, all with the same size & growth
ALTER DATABASE [tempdb] ADD FILE ( NAME = N'tempdev1', FILENAME = N'E:SQLData empdb1.mdf' , SIZE = 1048576KB , FILEGROWTH = 5%)
ALTER DATABASE [tempdb] ADD FILE ( NAME = N'tempdev2', FILENAME = N'E:SQLData empdb2.mdf' , SIZE = 1048576KB , FILEGROWTH = 5%)
ALTER DATABASE [tempdb] ADD FILE ( NAME = N'tempdev3', FILENAME = N'E:SQLData empdb3.mdf' , SIZE = 1048576KB , FILEGROWTH = 5%)
-- Now restart the instance.
Also, what are peoples thoughts on percentage growth for tempDb? I've read that it's not recommend and yet it seems to be the norm.
I am new to using SQL Server but will be having to get to know it at work and have downloaded SQL Server 2005 Express to tinker with at home.
What are the easiest methods to set up a relational database?
Are there any tools you can download to give SQL Server or the express version functionality similar to Access?
Can you get a tool to allow you to not only design tables in design view but then go on to create relationships and a database model?
Please excuse my ignorance if this is possible already, can anyone provide any links to getting started links?
There seems to be plenty out there about writing queries and stored procedures etc. but I haven't found as much about setting up a database from scratch.
Thanks in advance,
JJJ.
I am trying to join two table from two different databases.
Database 1 = Agent and Table = Stats
Database 2 = Amount and Table = Sales
The common field is Expr1 (table1) and Initials (table2)
I recently was given control of an SQL server that stop working.
We had no past backups of the database and the only file we had was the .mdf
I decided to install a fresh version of SQL 2000 on another server and created a database using enterprise manager with the same name as my database called SWATraining
I then stop the sql server
the first thing that I notice is that the .mdf that enterprise manager created had _DATA at the end of the name. Thus I renamed the the orginal .mdf SWATraining_Data and
copied it to the
C:program filesmicrosoft sql servermssqldata
when I started SQL the database was greyed out and had (suspect)labeled
How can I recover the database when the only file I have is the .mdf file??
I even tried
sp_attach_single_file_db @dbname = 'SWATraining',
@physname = 'C:Program FilesMicrosoft SQL ServerMSSQLDataSWATraining.mdf'
but reason this error
Server: Msg 5172, Level 16, State 15, Line 1
The header for file 'C:Program FilesMicrosoft SQL ServerMSSQLDataSWATraining.mdf' is not a valid database file header. The FILE SIZE property is incorrect.
I tried samples of FileTable in SQL Server 2012 to store files in database. I have following questions.
1. When I right click on FileTable, and say Explore File Table Directory, I was taken to a directory. But if I search the same directory in my server/machine, the directory is not available.
2. Can I use some external server/disk as a directory for FileTable?
3. How easy is to use FileTables using .Net web applications?
Hi,
Hopefully someone can help me with this.
When I downloaded the files and after burning the DVD to create the DVD to setup the 7031A - Upgrading to Microsoft SQL 2005 course, all of the executable files (BCBase.exe, Upgrade.exe, InPlace.exe, SideBySide.exe and SQL7Upgrade.exe) are not in the Setup/Drives folder...the only item in that folder is a file called "placeholder.txt".
Please, if anyone knows how to get these exectuables let me know.
Thanks,
David - EduCorp Training & Consulting
We have multiple databases on a single instance in an OLTP environment. I have my data files on a separate SAN LUN from my transaction log files (and a few NDFs split out onto additional LUNs). I was wondering if there is a performance benefit to putting each LDF file on its own LUN? Or at least my few busiest LDFs?
We are currently on 2012, but I'm having to put together specs for a 2014 installation and need to answer this question without having an environment in which I can benchmark different setups. I just want to hear whether or not others have done this (why or why not?).
Hi All,
Are there any MDS Descriptor Files associated with Databases Files? Are these associated with transaction logs? As per my information there are .mdf , .ndf and .ldf files.Could anyone please guide me ASAP,as there is an urgent requirement from one of the clients?
Thanks in Advance
Gaurav Matkar
Hi,
We are developing products to target thousands of clients.
Front-End : C#.Net 2005
Back-End : SQL-Server 2005
We started an Inventry control project (Product) with 10% percent of data as we didn't meet any clients (User requirements). I mean, I could not get complete user requirements. version by version, I need to change the database dynamically, and it should support backward compatiblity. While altering the database the existing primary keys are affecting. It seems I need to complete reconstruction of data Layer, Business Layer and Presentation Layer. How to avoid reconstructions in such scenerios? How database designers designing databases for a product like this? Or should I need to create "Table_Name_Serial_Number" (identity data type) column for each table and column to be primary key of that table?. Please help me!
Thanks in advance,
R. Pandiarajan.
I am trying to create sql script which will check all databases file size and then depend on the size set up auto growth different sizes with MB.
for example, sql server has 50 databases with different sizes, i would like to change auto growth all databases with different autogrowth size MB.
File size Data Autogrowth Log Autogrowth
0MB - 250MB 25MB 25MB
250MB - 1GB 250MB 250MB
...........................
We are planning to move all of our System Center Databases that reside on front end servers to each system center application to a centrally located SQL 2012 server. We'd like to centralize everything and have our DBA care for the server. here is our setup:
SCOM has 1 monitoring and 1 Data warehouse server. SCCM has 1 server with all roles on it. DPM database is on the same server as Application. Same with SCVMM. I have 2 questions regarding this move:
1. Can I have all these databases running on 1 SQL instance?
2. Is there a best practice document that highlights steps and "gotchyas"
i need to change the production database structures with multi language Support , datatype from VARCHAR to NVARCHAR in all SQL Objects SQL Objects: Tables, Functions, Stored Procedures...Some of production Tables with 15 Croces records with 10 indexes.how to change the entire databases through SQL scripting
View 3 Replies View RelatedIn my enviornment i have one database with 6 ndf files and 5 ldf files and one mdf file.Actuvally what i am looking is to merge 6 ndf files into one ndf file and 5 ldf files into one ldf file.is it possible to do like this? , i tried using MOVE and TO option while backup is restorating but getting below error messages.
ERROR:
Msg 3176, Level 16, State 1, Line 4
File 'J:NDFabc.ndf' is claimed by 'Finance_data2'(4) and 'Finance_data1'(3). The WITH MOVE clause can be used to relocate one or more files.
I like to move my database from a directory to another (on different drives foir exemple).
Is there an easy way to do this kind of job ?
Hi, I've created a new filegroup, then added to it a new filename, then create a table pointing to this filegroup. So far everything is ok, but if I want to revert the process in this way:
1- Drop the table - OK
2- Drop the filegroup - OK
3- Drop the filename - ERROR: Msg 5009, Level 16, State 9, Line 2
One or more files listed in the statement could not be found or could not be initialized.
When I query a catalog view with this query:
Select * From Sys.Database_Files
I get the file that I had recently deleted, it is offline but I can not delete it using the ALTER DATABASE instructions.
This is the code I use:
Use [TESTING ]
Go
-- Add a FileGroup to the Database
Alter Database TESTING Add FileGroup FG01
Go
-- Add a file to a FileGroup
Alter Database TESTING Add File (
NAME = TESTING_DATA01,
FILENAME = 'D:Sql ServerDataTesting_Data01.ndf',
SIZE = 1 MB,
MAXSIZE = 10 MB,
FILEGROWTH = 1 MB
) To FileGroup FG01
Go
-- Create the table using a specific FileGroup
Create Table TABLE1 (
Id Int Not Null,
FirstName VarChar(30) Not Null,
LastName VarChar(30) Not Null,
BirthDate SmallDateTime
)
On FG01
So far everything works ok, but in the next code there is an error:
-- Delete the table
Drop Table TABLE1
Go
-- Remove a FileGroup from the Database
Alter Database TESTING Remove FileGroup FG01
Go
-- Try to remove the file
Alter Database TESTING Remove File TESTING_DATA01
When I try to remove the file there is an error:
"Msg 5009, Level 16, State 9, Line 2
One or more files listed in the statement could not be found or could not be initialized"
Is there some sort of command that can tell me which mdf and ldf files are associated to which databases?
Thanks,
Ninel
Hi,
I have some doubts about Shrinking databases or files.
while shrinking a file, I learned that we are alllowed to shrink more than the minimum size of the file, does not it bring damage to the data in that file ??
I am getting this massage in error log .
"Database XYZ has more than 1000 virtual log files which is excessive. Too many virtual log files can cause long startup and backup times. Consider shrinking the log and using a different growth increment to reduce the number of virtual log files."
I am using sql server 2008r2.
I have a database which has log file size 300 GB. As the drive is filling up i need to clear the space on the drive, for that i have to shrink the log file.
Unfortunately i dont have option to take backup of the database.And i am not able to shrink the file now. Is there any way to shrink the log file with out taking backup of it ?
The size of log is around 600MB and only 20MB is used...
I tried truncating the log using dbcc shrinkfile..but it doesn't truncate
Any help would be appreciated....
Thanks.
We are looking into sql 7.0 and would like to know how it handles excessively sized databases. If we restore a 6.5 database into a db on 7.0 will it automatically resize to free up disk space or will it only work for new databases which have install scripts run on it.
View 2 Replies View RelatedHi all, hope someone can give me some guidence on this.
We have a database on SQL 6.5 sp5a that is 25GB in size. The transaction log is 10GB in size. We do transaction log dumps every hour throughout the day. On a very heavy day the transaction log backup device will be just over 2GB in size. Around 8GB wasted because no-one knew how much space would be used.
Is there an easy way to resize the transaction log so we can reclaim some space. I was thinking of creating a new smaller database device, then using sp_logdevice to use the smaller device as the log and get rid of the 10GB device.
Any thoughts, ideas would be great.
Thanks
Phill
My transaction log has grown to 2 Gig. But when I do a truncate transaction log. It is only 18Mb. How can I resize the physical disk space of the transaction log. I want it only to be 100 Mb. It is just wasting the disk space. Please help!
View 1 Replies View Related
i have a report with several rectangles. How do I make the size of the rectangles fixed so that they do not resize automatically?
They appear fixed until i put a table inside. once I do that it automatically resizes
Hi,
I'm trying to list all database locations with SQL command, but getting lost here :(
So, anyone any idea how to get databasefile locations from server?
All I need is like: 'C:Program FilesMicrosoft SQL ServerMSSQLDatamaster.mdf' and others.
Thanks.
We have two db's. One live and one test.When I right click on the live one in SQL Enterprise Manager andselect properties -> Data Files ->File Name is LIVE.MDFLocation is F:DataLIVE.MDFWhen I right click on the test one in SQL Enterprise Manager andselect properties -> Data Files ->File Name is LIVE.MDFLocation is F:DataTEST.MDFSame thing applies to Transaction log files too.My concern is File Name is same in both the above cases even thoughthe location is different. What are the consequences of this.Thanks for your helpGVV
View 1 Replies View Related
Hello,
I need to move all log files for my SQL 2005 databases to another drive. I don't wish to shrink the files, I need to move the logs to another drive spindle. I did find an article (Article ID: 224071) that describes moving both the database and logs using sp_detach and then sp_attach. What is the best way just to move the logs to another drive on the same server, and that keeps the databases in their original location?
Thanks.
Hello there,
I've been told that it is good practice to keep mdf and ldf files in another location... We have it in place for all our user databases, however mdf and ldf files for our system dbs are still at the same location. I was wondering what is the right way of splitting those should be?
Here's my question, hopefully one of you can help me:
When you allow your log file to grow on its own, eventually it uses up all your diskspace . . so you truncate it. However, when you truncate it, it doesn't *resize* the log file, so your still out of disk space, it simply clears up room within the allocated "block" or what-not so that the db can continue to make log file entries.
So, does anyone know of a way to resize that log file without losing it's data? For example, we were thinking about truncating it, backing it up, dropping the original, and then restore the log file from the "truncated backup." Anyways, there must be an easier solution (if that one even works!) . . so please, if you have any hints, let me know.
Thanks,
David P.
I have a database which is quickly filling up its allotted data space (the database is 500MB, and the device is also 500MB). I wanted to know whether it was an acceptable procedure to:
-Dump the database
-Create a new 600MB (or more) device
-Create a 600MB database for load on that device
-Restore the dump to it.
Are there data integrity/corruption/fragmenting issues with this method? The alternatives are simply expanding the database across another device (or on the same device using DISK RESIZE), or using scripts and bcp to recreate the database from scratch. These do not seem ideal. Any opinions or comments would be greatly appreciated.
MR
Is there a way to shrink database devices in SQL Server 6.5 Other than dropping and restoring?
View 1 Replies View Related