I ran the following query to get the log file sizes for all databases:
select (size*8.00)/1024,filename from master..sysaltfiles
When I compared the results from this query with the transaction log properties in EM, not all of the sizes match.
For example, EM shows the transaction log size for tempdb to be 2 MB but the results of the query shows that transaction log size for tempdb is .5 MB. Which query can I run to get the numbers that would match between two? Thanks.
Is there a query I can run to retrieve a list of all tables and their sizes in a database? I want something that is like the feature in Enterprise Manager when you click on a database and then the 'Tables & Index' link. It lists the tables and their respective size. I want to push this into a spread sheet.
The reason why I am doing this is the compare data between 2 different databases. Since I cannot find a tool that will compare the data, the closest I can get (without bcp-ing out all data and comparing) is to look at the sizes of each table.
When you have the autogrowth turned on for log files. What happens when you put a max file size on it? Will just overwrite the old logs to keep the file at the max size or will it just create a new file every time it hits the max size?
Hi, my log files are growing like anything. One of my log file size is 20GB. How i have to reduce the log file size. If i run DBCC command is it come backs... Pls tell me the way how i have to find the free space and reduce logsizes. After taking backups also my log file sizes are not reducing.
I am in the middle of capturing a workload to try and tune a SQL instance and was wondering what kinds of sizes people capture in terms of traces. I am only 1 day into a capture and I believe a typical workload would be a week long capture and I am already at 10GB of files. I am only capturing rpc_completed and sql_batch_completed.
What sizes of workloads do other people capture and then where do you analyse them, do you have particular dedicated server for this kind of thing as at present I am looking to use my local PC. Also what rollover file sizes do people tend to use, I am currently using 1GB.
I have one .mdf and two .ndf files on the same drive. The .mdf file size =275GB, one .ndf file size = 300GB and other .ndf file size = 135GB. Is this normal to have 3 different file size? if not what can I do to fix this? I don't have option to make all files to initial size equal to 300GB as a .ndf.If I have to add a .ndf file (in case of running out the above drive), what initial file size should I set up for new file on new drive? And how data gets distributed across all 4 files (including new .ndf on different drive)?
Is there a fairly quick and dirty way to shrink the file size allowedfor a DB on SQL server after the DB is in use?Our old DBA allowed 3 gb for log files and we don't need nearly thatmuch. However, the space is still being used and reserved. Can Ishrink that "maximum" and clear up the space?I don't want to just copy all the tables and primary keys and open anew one with the new data, though that is plan A right now.I cant just change sysfiles, it yells at me.
I'm using SQL Server 2000 and need to restore a large database onto a different node. The problem is the original database has a 74 gb first datafile and the node where I need to restore it doesn't have a single drive that big. I'm trying to use a backup of the original database and restore it into an existing database on another node and am using the move options to put the files in the right places.
Is there a way I run the restore to split the 74 gb datafile across drives on my target node?
I have an instance with 4 datafiles for tempdb each set at initial size of 4G and growth rate of 100MB. After some time the initial file sizes seem to have changed automatically. They now read 3962,100,3688 and 2847 respectively. Is this something done by SQL Server itself? I cannot imagine that it was done manually.
I don't think there was a restart after the initial sizes of 4G were set, could this be related to the problem?
Hi Every one One thing i found out that my 2 databases in server A Has same logical file name . I have try to change the name but it is not allowing me to change. I had refer BOL it says that we should have unique logical file name in a server for each database. Question i have is does it going harm me i don't know this if any one u know please suggest me. Problem I already started getting is I do backup 5 small databases of 10 to 20 mb in one tape only. The 2 database which has same logical file name out of which one is getting copied & another is not ,All other database it backup perfecly. But at the same time in a hard disk if i take backup of this database in a same device it works perfecly so i don't understand where the problem is If any has any idea please suggest me Thanks Nirmal.
As part of monitoring i want to hourly check the data file percentage used for each database, to monitor growth and know in advance when a data file will be full.
However i do not want to write a job for ever single database , this instance may have up to 100 databases that = 100 jobs.
So i want to write a job that checks the percentage space used for all databases.
My first dilema is that i cannot loop through databases dynamically, by that i mean if i use a cursor that loops through database names, and i dynamically build sql the say 'USE @DBNAME' and execute it the cursor context stays local you do not actually change database context.
So how do i loop though databases, i have found
sp_msforeachdb, however this is undocumented in BOL.
Secondly how do i check the percentage of space used for the data file or files for a database, i could use DBCC SHOWFILESTATS, however this is also not documented in BOL.
Obviously i would rather use documented methods.
So bottom line what tsql could i use to check the percentage of file space use for all databases?
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
Anyone here with a ready to go sqlscript that lists all db's, files, sizes, owner etc? I guess it's a combination of sp_databases, sp_helpdb and sp_helpdb [db].
I am monitoring the data file growth of the databases in a table. Every week I review to see how much space is left on each database. I am thinking of writing a query where the current free space left is less than 20% of the file size, it sends out an alert to me, so that I can manually resize the file . Is it a good practice to resize the data file manually? If so I believe this need to be done at the time when the server is least busy since it can slow down the database. Also do I need to re-index the tables once the data file is resized?
Does anyone know of a quick way to find out what the largest indexes on a database are? I have a number of tables and was wondering if there's a stored proc or query that I can execute that will list the indexes and their size in order by size? Thanks
Does anyone know of a quick way to find out what the largest indexes on a database are? I have a number of tables and was wondering if there's a stored proc or query that I can execute that will list the indexes and their size in order by size? Thanks
I'm putting together a manual system that tracks data growth in a certain database. I was going to use sp_spaceused as a part of it, but then realized the datatypes for size are CHAR, not INT or BIGINT. I was going to do counts, averages, etc. on those columns but that wouldn't work against a CHAR field obviously. I could easily write a little something to strip out the KB, but was hoping there was another way to get those figures.
Secondly...has anynoe seen a stored procedure/code/etc. that just calculates the largest/smallest/average row size for a table? I haven't been able to find anything anywhere...
I am currently cleaning up my database to get its total size down and am not sure how nvarchar and varchar work exactly.
When defining the length of a varchar or nvarchar in enterprise manager, will that effect the size of the entry (as far as data size) no matter what the length of the entry? In other words, will there be a difference in Data Size for an entry with the length of 4 characters with a definition of varchar(4) versus an entry with the length of 4 characters with a definition of varchar(50).
****If there is no difference, is there any reason in trying to best guess the size to give nvarchar or varchar columns? It would seem easier to just define the lengths of columns which need variable lengths to 200 or 400 just to save time in not trying to best guess what the size might be...*****
Hi, I am looking to runa query to get the sizes of the tables in my SQL 7 DB. I know I can access the info in Enterprise Manager, under "Tables & Indexes". But I need to get this info via a query. I need rows and size. I figured out how to get rows through the sys tables: select sysobjects.name, sysindexes.rows from sysobjects,sysindexes where sysobjects.name = sysindexes.name and xtype = 'U'
Is the size of each table stored in a sys table as well? I can't find it.
Hey all, Got a little problem. have 2 matching tables on different servers with the EXACT same column layout and data (the tables are being replicated with MSSQL7) and one table is 200MB while the other is 2000MB. I'm running MSSQL7 SP2. Any ideas???
I have inherited a number of databases which were substantially over sized when they were set up. I'd like to reduce both the log and database files to be smaller than their original sizes, what's the easiest way to do this? If anyone has any experience of doing this please reply.
We are looking at installing a new Oracle server for a client but have been told that they have used Oracle in the past but had a lot of problems with slow response even though the bandwidth on the WAN was barely being used. He says that this was due to the fact that Oracle sends out very small packets across the network meaning that there are hundreds of packets being sent out. This caused a problem on the routers being used as it was killing the processors. Is this still the case and have you had other reports of slow response of this nature?
Ok, I have a new one. Several of my devices are showing with negative sizes when viewed in edit in enterprise manager. I cannot edit them as the change now button is grayed-out. Oddly enough they are all located on the same drive. The master (on C drive), and the tempdb (on D drive) both show as the default device. I am very confused. User access to the information is fine. What gives?
Can any one direct me to sources for best practices of field types and sizesto use for commonly used information such as address, names, city, businessnames ....Thanks, Brian
