SQL Server 2008 :: TempDB Datafile Initial File Sizes Changed?

Mar 18, 2015

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?

View 1 Replies


ADVERTISEMENT

SQL Server 2008 :: Initial Size For TempDB Data And Log File?

Sep 12, 2011

We have installed SQL Server 2008 R2 SP1 instance and it's having Share Point 2010 databases.

We have 2 dedicated drives for Tempdb on SAN with 50 GB space. Both tempdb data & log files are created with default size. I would like to presize them.

What are the best values to start with?

U ->Tempdbdata having tempdb.mdf file
V->Tempdblog having templog.ldf file

View 9 Replies View Related

SQL Server Admin 2014 :: How To Allocate The File Sizes (Disk Partition Alignment For Tempdb)

Jun 29, 2015

I have question about tempdb needs to be configured 100GB 64kb block size.its fresh installation.

how to allocate the file sizes.still im not sure how many log files needs to be created with 100GB equals to 64KB block size.

what is 64KB block size and how to divide the logfiles 64KB into the 100gb or 50GB?

what is 64 KB cluster has 128 sectors?

tempdb drives should be formatted with a 64K allocation? how many files needs to created for good performance with 50GB or 100GB? ot 1TB

View 3 Replies View Related

SQL Server 2008 :: MDF And NDF File Sizes

Feb 26, 2015

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)?

View 2 Replies View Related

Moving Tempdb Datafile & Logfile

Jul 16, 2001

Is it possible to move (detach and attach) the physical data and log file (.mdf &.ldf) to another location?

View 1 Replies View Related

Tempdb Initial Size

Mar 3, 2000

We had a runaway query which built the size of tempdb to 24000mb. Then someone changed the unrestricted file growth property to restricted growth while the size was 24000mb. Now I can not reduce the initial size. I have set the property back to unrestricted file growth. I have shrunk the tempdb and available space is almost 24000mb. I have stopped sqlserver. I even deleted the existing tempdb.mdf & tempdb.ldf files. But when SQL server is restarted, the initial size is set to 24000mb. It will not let me reduce the size. Is there anything short of manipulating the system tables to reduce the size back to 500mb?

View 5 Replies View Related

Remove Initial MDF From TempDB?

Nov 6, 2015

SQL 2008R2 

Added four new equally sized .mdf files to a production tempdb last night and restarted the instance.  I was hoping to remove the initial file as it's not required and does not match the size+growth of the others.  The option to 'remove' it is greyed out in the files tab of the GUI so I'm assuming that ALTER DATABASE [tempdb]  REMOVE FILE [tempdev] will also fail.  

Do I need to migrate any data in this particular .mdf before it can be dropped?  Or would the simplest approach be to change the size of this file to match the others and drop tempdev3? 

This is the output from sp_helpfile

View 3 Replies View Related

SQL Server 2008 :: How To Improve Speed Of Initial Query Vice Subsequent Queries

Apr 23, 2015

I have a pretty large DB and a fairly complex query. If I drop buffers and clear cache the query runs in 20 seconds returning 25K rows. Subsequent runs are 2 seconds. Is this the result of the results being cached, execution being cached, other? Are there good ways to close the gap between the initial and later runs? Does the cache stay present until the service restarts or does SQL recycle the memory and if so, based on what criteria?

View 5 Replies View Related

SQL Server 2008 :: Date Format Got Changed Between Two Different Version Of Server For Same Web Request

Sep 18, 2015

we are having the same code base in both the environment but we have different version of SQL Server Version(Env 1 - SQL 2012, Env 2 - SQL 2008). We are getting the expected result in Env - 1 but not in the ENV -2. So we tried to get the SQL trace for the web request we found that Date format getting changed.

2012 - Date Format in the SQL Trace for the request '2015-09-10 00:00:00' which is working as expected.
2008 - Date Format in the SQL Trace for the request 'Sep 10 2015 12:00:00:000AM' which is Not working.

Complete SQL Trace for Request.
Evn - 1 - SQL Server 2012
exec dbo.usp_GetWorkQueue @ClientName='CLT',@HasFilter=1,@FacilityName='',@StartDate='2015-09-10 00:00:00',@EndDate='2015-09-18 00:00:00',@OrderDirection='D',@StartIndex=1,@PageSize=20

[code]...

View 3 Replies View Related

Restoring To Another Server Issue With Data File Sizes

Apr 4, 2007

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?

Thanks for any help.

View 1 Replies View Related

SQL Server 2008 :: Find Out What Data Was Changed By A Stored Procedure After It Was Executed?

Jul 22, 2015

isn't there an automatic log of some sort to check and see what exactly was changed by a given SQL command? A stored proc was ran and I need to figure out what exactly it changed in the underlying table.

View 3 Replies View Related

SQL Server 2008 :: Changed Database From Full To Simple - Maintenance Plan Failing

Mar 6, 2015

One of my programmers changed their database from full to Simple recovery. Saw that my job that backs up the Full Recovery mode databases failed, so I moved that database to my Simple database backup job plan and removed it from the Full Recovery job. I am unable to remove the db from the Transaction Log task on the Full Plan because when I try to edit that job "Databases with Simple Recovery will be excluded"

My transaction log backups are still failing with the following error: "The statement BACKUP LOG is not allowed while the recovery model is SIMPLE. Use BACKUP DATABASE or change the recovery model using ALTER DATABASE. BACKUP LOG is terminating abnormally.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.Just want to remove that database so my Full Recovery backup job does not try to back it up.

View 2 Replies View Related

SQL Server 2008 :: TempDB Size Hovering Around 45%

Jul 27, 2015

My prod server (only default instance) is configured TempDB 1024 MB data and Log 200MB. when I run 'sqlperf logspace' it shows most of time around 45% 'log space used'. There nothing going on the instance when I ran 'whoisactive' and select * from sys.sysprocesses where dbid = 2!!!

So my questions are is this normal to see log space around 45%, how to find what what CAUSED the tempdb log space to grow 45%? Is there something to do about it?

View 6 Replies View Related

Maximum Datafile/log File Size

Dec 8, 2004

Is there any limit to the maximum size of a datafile or transaction log you can have with SQL Server 2000 on Windows 2000. Also is there a maximum size that should be adhered to for performance and admin reasons ?.

View 4 Replies View Related

Log File Sizes

Apr 28, 2008

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?

Thanks!

View 1 Replies View Related

Log File Sizes Are Growing Like Anything.

Nov 8, 2000

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.

Thanks!
Kavira

View 2 Replies View Related

Log File Sizes For All Databases

Jul 21, 2006

Hi All,

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.

View 3 Replies View Related

Large File (MDF & LDF) Sizes

Oct 1, 2007

Admin is kind of new to me. Could someone recommend how to reduce these file sizes?

In Dev, my MDF is 16 MB, LDF .5 MB.

I run a script to recreate the schema and insert rows on the Host.

When downloading a db backup from the Host and restoring it to Dev, my MDF is 24 MB, LDF 25 MB.

The same dataset grew by ~35 MB!!!

Any advice is welcome. Thank you. Rick

View 11 Replies View Related

SQL 2012 :: Trace File Sizes

Jul 30, 2014

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.

View 7 Replies View Related

Ex Post Shrinking File Sizes

Jul 20, 2005

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.

View 1 Replies View Related

File Initial Size

Mar 11, 2008

Via t-sql, how can I query for a file's initial size?

I want the same thing one finds by :
Start SQL Server Management Studio, view, object explorer, right click [dbname], properties, files, Database files: Initial Size (MB).

Many thanks.

Anything will give up its secrets if you love it enough. -- George Washington Carver

View 9 Replies View Related

Integration Services :: SSIS - Calling Windows Batch File Get Current Date Changed When Moved To New Server

Aug 20, 2015

We have a Job  that calls a SSIS package 2005 that does some processing and execute a BAT file.  This Job is being called by a web application.The BAT file creates a folder and named it based on the current date ( YYYY_MM) e.g 2015_07

echo %date:~10,4%_%date:~4,2%
md %date:~10,4%_%date:~4,2%
pause
exit 0

It was working okay in the SQL Agent 2005 server until we moved  to the new server SQL Agent 2012 using the same package SSIS package 2005. Now the issue is, instead of creating the folder based on YYYY_MM, it's now being created as YYYY_DD.I've checked the Regional settings of both server and they have the same "ENGLISH (United States) format. I even ran the code below and they're returning the same output echo %date:~ 10,4%_% date :~4,2%

I know the BAT file can be improved by not depending current locale in WINDOWS, but I just want to understand how this issue occurs and how does the regional setting being overridden?

View 2 Replies View Related

T-SQL (SS2K8) :: Initial Size Of Database File

Apr 9, 2013

How to get the initial size of the database file using T-SQL.

sys.master_files, sys.database_files, sysfiles, sysaltfiles --> gives only the current size and not the initial size.

View 4 Replies View Related

How To Change Initial Log File Size On Mirrored Database

Oct 23, 2015

I have a database I need to copy from a Prod server to a Dev server. There is not enough space on the Dev server. In looking at the size of the files on the Prod server, the Initial Size property for the transaction log on the Prod server is set to 100,000 MB though the log is using nowhere near that.

This is a mirrored database so the recovery model is "full". I know that to change the initial log size, I have to put the database in 'simple" recovery model. Is this possible? Can I just:

1. Pause the mirror
2. Switch recovery model to simple
3. Change the initial size property to something smaller.
4. Shrink the transaction log
5. Change the recovery model back to full and resume the mirror?

I honestly don't know if the transaction log is needed on the Dev server. Meaning I may just be able to restore the transaction log to a different location on the server and delete it so that  new one is created.

View 2 Replies View Related

SQL Server 2008 :: SSIS Copy File From SharePoint Library Using File System Task Permissions?

Jun 19, 2015

Historically I've always written a VB script to copy a file from a sharepoint library. I don't like this method because I have to input a username & password in the script and maintain a config file.

Yesterday I was playing around with using a file system task. The sharepoint file has a UNC path so why not? I created a simple test package with a single file system task that copies the sharepoint file (addressed via UNC) to another network location. Package runs fine locally.

I try running on our utility server but am getting a "The file name [SHAREPOINT UNC PATH] specified in the connection was not valid" error. Package is running with a proxy on the server and the proxy account has the same permissions to the sharepoint site (so far as I can tell) as me.

View 0 Replies View Related

SQL Server 2008 :: Log File Space Is 5 Times The Data File

Mar 16, 2015

one of my database data file is 100 GB and the log file is 500 GB.DB is in full recovery model and the transaction logs happen once in 6 hours.Even then, the Database log file isn't reducing in size.

View 9 Replies View Related

File Name To Be Changed

May 21, 2007

Hello,



I have developed a report based on a stored procedure which will generate an excel file. There is a requirement that this file needs to be sent to an external domain with the name changed accordingly i.e., a date is to be suffixed to the file after the name has been changed based on their naming convention.



I would appreciate, if anybody has done so and can give me some steps for me to work out.



TIA

View 3 Replies View Related

SQL 2012 :: TempDB Log File Usage Constantly Rising And File Keeps Growing?

Jun 16, 2014

The TEMPDB transaction log file keeps growing.The database server is new and the transaction log was presized to 1 GB on installation. After installing a number of databases, the log file grew over a day to 38GB. Issuing a manual checkpoint was the only way to free some space to allow it to be shrunk back to a usable size. The usage of the file is still going up.

I am struggling to find what process is causing the log to be used so heavily. Looking at the log reuse wait desc for tempdb returns "Nothing" and tempdb itself isn't being used very much or growing in size.

View 9 Replies View Related

Sql Server - Datafile Location

Aug 3, 2001

Hi,
Is there any way to change the location for the datafile. I need to change the drive from say c to d because it is filling up. Is there anyway to do this or do I have to recreate the database from scracth. I have a whole lot of data in the database already.

Thank you all.

View 1 Replies View Related

Moving Datafile On SQL Server 7.0

Dec 14, 2004

Hi All,

I have a database -- MDB -- with datafile for data and transaction log under the folder d:mssqldata . Now i want to move the data file from d: to e:, say e:mssqldata . Can someone let me know if this is possible under SQL server v7.0 and if so, how

View 2 Replies View Related

SQL Server Admin 2014 :: How To Add Datafile To Mirrored Database In Primary Server

Aug 5, 2015

I need some clarification about adding file in to mirrored dataabse in primary server without downtime and breaking the mirror server.

In our environment we are using monutdisks in both the servers. in primary for ex we have F drive for data files under mount disk 3 in mirror server also we have same drive but in mount drive2.

As per my knowledge if it is same drives we can add the ndf files in the primary that will reflect on mirror. but in current situation i am confusing about mount points with different names.

View 3 Replies View Related

Wich One Is More Efficient ? Datafile Or Attached On Server ?

May 8, 2008

Hello friends !
Wich one is more efficient way to use a sql mdf file in Sqlexpress ?
attaching a mdf file on sqlexpress ( and use initialcatalog in connection string ) or use AttachDbFilename on connection string directly ?
Is there any difference in performance and speed ?
Thanks a lot

View 1 Replies View Related

Setup And Upgrade :: MDF File - Database Files Icon Changed Or Grayed Out

Jun 17, 2015

I tried to open mdf file without any success. Then, all database files icon were changed or grayed out. How can i repair or replace the file to it's original file? ex... the file named "event.mdf" has now a wrong icon not a database icon like before.

View 11 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved