locked
An error occurred while executing batch. error message is :There is not enough space on the disk RRS feed

  • Question

  • Hi,

    I have a View <View_XYZ> which is basically union 15 views from another 15 databases in the same server.

    the View <View_XYZ> is look like

    select <Fieldname_1>,<fieldname_2>.....<filedname_n> from DB_1.dbo.view_a
    union all
    select <Fieldname_1>,<fieldname_2>.....<filedname_n> from DB_2.dbo.view_a
    union all
    .

    .

    union all
    select <Fieldname_1>,<fieldname_2>.....<filedname_n> from DB_15.dbo.view_a

    and in all 15 databases the view_a basically selecting data from a table which contain almost

     
    5767379
    1119268
    6216002
    1576537
    1161686
    685650
    1515197
    580015
    508097
    1470459
    720379
    0
    0
    0
    1760279

    hence in the main view View_XYZ there are almost 23080948 rows. and the datafiels and logfiles are pointed to M drive and in M drive there are almost 18 GB space left.

    The main database size is 56 MB and all other 15 databases are almost 15000 MB sizes.

    When I executing the View_XYZ from the main DB i am getiing and error and stop execution .. The error is " An error occurred while executing  batch. error message is :There is not enough space on the disk" where as in C:\ drive almost 50 GB space left and in M:\ drive where the log and datafiles are kept almost 18 gb space left.

    and after this error occoured I cant run any querey.. like sp_helpindex View_XYZ
    the same error occoured " An error occurred while executing  batch. error message is :There is not enough space on the disk"


    Help me..

    SD
    Monday, November 23, 2009 4:36 PM

All replies

  • Verify the tempdb and transaction log file sizes, and that the tempdb is set to 'AUTOGROW'.

    In your main View_XYZ, there is over 23 million rows. I could see this requiring extreme amounts of tempdb space.
    You may be only one person in the world, but you may also be the world to one person.
    Saturday, November 28, 2009 4:24 AM
  • That seems to indicate that the tempdb data file is about 55 GB.
    Is that correct?

    Is there additional space on the disk holding tempdb?
    Is tempdb set to Autogrow?

    At the moment, it appears that you have a very large, and mostly empty tempdb. BUT when this operation occurs, the file fills up until it eventually is out of space, and cannot get additional disk space.
    "You cannot do a kindness too soon because you never know how soon it will be too late." -Ralph Waldo Emerson
    Tuesday, December 1, 2009 7:18 AM
  • Hi.. Arnie.. thanks for the reply.. But while executing the query (select * from View_XYZ) .. in another query windown I checked the spaces of TempDB

    the result is as follows..

    Database Size : 37888.00 MB
    Unallocated Space : 17401.48 MB

    Reserved : 6672 KB
    Data : 3560 KB
    Index : 1848 KB
    Unused : 1264 KB

    All the log and data files of TempDB pointed to M:\ Drive and in M:\ drive there are almost 18941 MB space available

    The log file settins as Unlimeted grow
    Size : 20971520 KB
    Maxsize : Unlimited
    Grow : 0 KB
    Filename : M:\NAMF218192_TempDB_Logs_1\Logs\TempDB\templog.ldf 

    So I guess there is no problem with tempdb.. What do you say?
    SD
    Tuesday, December 1, 2009 7:23 AM
  • Arnie ..

    There are 16 datafiles (1 Primary and 15 secondary) in tempdb and one log files
    All the data and log files are pointed to M:\ Drive and in M:\ drive there are almost 18941 MB space available

    The log file settins as Unlimeted grow
    Size : 20971520 KB
    Maxsize : Unlimited
    Grow : 0 KB

    as the Maxsize is defined to Unlimited and Grow=0 (thats indicate Autogrow set to off)

    The Database Size 37888.00 MB (37888.00 MB/1024)=37 GB right?


    Filename : M:\NAMF218192_TempDB_Logs_1\Logs\TempDB\templog.ldf 
    SD
    Tuesday, December 1, 2009 8:58 AM
  • HI,

    shrink your log file and set database property to simple...

    Shamas Saeed (if Post helpful please mark as Answer) MCITP-DBD 2005 http://sqlservercoollinks.blogspot.com
    Tuesday, December 1, 2009 9:11 AM
  • both the database recovery model set to simple..


    SD
    Tuesday, December 1, 2009 9:27 AM
  • dbcc shrinkfile('logfilename',sizeremains)


    but remember uncommited transactions might loss
    Shamas Saeed (if Post helpful please mark as Answer) MCITP-DBD 2005 http://sqlservercoollinks.blogspot.com
    Tuesday, December 1, 2009 9:28 AM
  • use following link and run query on your db and see which table is using max space.

    http://sqlservercoollinks.blogspot.com/2009/10/finding-used-table-space-with-data.html

    there might be some unwanted table like error log and message log if you are maintaing.
    Shamas Saeed (if Post helpful please mark as Answer) MCITP-DBD 2005 http://sqlservercoollinks.blogspot.com
    Tuesday, December 1, 2009 9:31 AM
  • My problem is something else.. What I will do to get all tables spaces? what space occupied each table? I have a simple problem if you go throw the chain. I have a view in a database which is combining another 15 views inside from 15 other database in the same server. When I executing the main view I am getting error.. (the main view contain almost 2311793 rows.
    The error is " An error occurred while executing  batch. error message is :There is not enough space on the disk"
    SD
    Tuesday, December 1, 2009 9:46 AM
  • OK.. Sorry i did not go through the whole chain

    in my knowledge. You can convert your view from simple to Indexed, because that converts it on physical space. It is not going towards space and main problem is memory.
    Shamas Saeed (if Post helpful please mark as Answer) MCITP-DBD 2005 http://sqlservercoollinks.blogspot.com
    Tuesday, December 1, 2009 9:51 AM
  • I am executing select * from <View>

    So do you think any view will help ?
    SD
    Tuesday, December 1, 2009 10:13 AM
  • Yes of couse...


    http://www.oraclebrains.com/2006/12/differences-between-normal-views-and-materialized-views/


    logical or normal view is slow then physical materialized view.
    Shamas Saeed (if Post helpful please mark as Answer) MCITP-DBD 2005 http://sqlservercoollinks.blogspot.com
    Tuesday, December 1, 2009 11:01 AM
  • But the problem while creating Index in the main view.

    Because it is consisting of another 15 view from other 15 database in the sale server

    if we create index on the main view then we have to alter it as WITH SCHEMABINDING option and for that we had to mentions all column name from the other 15 inside view.

    and we cant code like that way

    select a,b,c,d .. from <dbname>.dbo.<view1>
    UNION ALL

    select a,b,c,d .. from <dbname>.dbo.<view2>
    UNION ALL

    select a,b,c,d .. from <dbname>.dbo.<view3>

    For SCHEMABINDING we had to express two part name. So here we cant mention the <dbname> .. and then how can we fectch data from other 15 databases
    SD
    Tuesday, December 1, 2009 11:14 AM
  • I am not sure that we have yet the correct understanding of the files.

    Please execute sp_helpFile and report your findings.

    What is the actual filesize of tempdb.mdf?
    (If tempdb is scattered across several filegroups, what is the size of each secondary file?)
    What is the actual filesize of tempdb.ldf?

    Using Windows Explorer,
    Verify the 'free space' on the disk that contains tempdb.
    Verify the 'free space' on the C:\ drive


    "You cannot do a kindness too soon because you never know how soon it will be too late." -Ralph Waldo Emerson
    Wednesday, December 2, 2009 6:23 AM
  • Hi Arnie

    The TempDB files details..

      tempdev 1 M:\Databases\TempDB\tempdb.mdf PRIMARY 2097152 KB Unlimited 0 KB data only
    templog 2 M:\Logs\TempDB\templog.ldf NULL 20971520 KB Unlimited 0 KB log only
    tempdev1 3 M:\Databases\TempDB\tempdev1.ndf PRIMARY 1048576 KB Unlimited 0 KB data only
    tempdev2 4 M:\Databases\TempDB\tempdev2.ndf PRIMARY 1048576 KB Unlimited 0 KB data only
    tempdev3 5 M:\Databases\TempDB\tempdev3.ndf PRIMARY 1048576 KB Unlimited 0 KB data only
    tempdev4 6 M:\Databases\TempDB\tempdev4.ndf PRIMARY 1048576 KB Unlimited 0 KB data only
    tempdev5 7 M:\Databases\TempDB\tempdev5.ndf PRIMARY 1048576 KB Unlimited 0 KB data only
    tempdev6 8 M:\Databases\TempDB\tempdev6.ndf PRIMARY 1048576 KB Unlimited 0 KB data only
    tempdev7 9 M:\Databases\TempDB\tempdev7.ndf PRIMARY 1048576 KB Unlimited 0 KB data only
    tempdev8 10 M:\Databases\TempDB\tempdev8.ndf PRIMARY 1048576 KB Unlimited 0 KB data only
    tempdev9 11 M:\Databases\TempDB\tempdev9.ndf PRIMARY 1048576 KB Unlimited 0 KB data only
    tempdev10 12 M:\Databases\TempDB\tempdev10.ndf PRIMARY 1048576 KB Unlimited 0 KB data only
    tempdev11 13 M:\Databases\TempDB\tempdev11.ndf PRIMARY 1048576 KB Unlimited 0 KB data only
    tempdev12 14 M:\Databases\TempDB\tempdev12.ndf PRIMARY 1048576 KB Unlimited 0 KB data only
    tempdev13 15 M:\Databases\TempDB\tempdev13.ndf PRIMARY 1048576 KB Unlimited 0 KB data only
    tempdev14 16 M:\Databases\TempDB\tempdev14.ndf PRIMARY 1048576 KB Unlimited 0 KB data only
    tempdev15 17 M:\Databases\TempDB\tempdev15.ndf PRIMARY 1048576 KB Unlimited 0 KB data only

     You can see there aer 1 log files, 1 primary datafiles and 15 secondary datafiles.

    C 48492 MB
    D 56972 MB
    G 18891 MB
    H 18941 MB
    I 18874 MB
    J 18933 MB
    K 18941 MB
    M 18941 MB


    you can see above.. C:\ drive space almost 47 GB and M drive where all the user db and tempdb files are kept all about 18 GB space available.

     


    SD
    Wednesday, December 2, 2009 7:43 AM
  • What is @@version?

    If the server is rebootable, do you get the same error after reboot when the tempdb is empty?

    Does the error message have a number with it? If not it does not come from SQL Server.

    How do you call the view exactly?



    Kalman Toth, SQL Server & BI Training, SSAS, SSIS, SSRS; http://www.SQLUSA.com
    Wednesday, December 2, 2009 9:28 AM
  • Hi..

    Version >>Microsoft SQL Server 2005 - 9.00.4035.00 (X64)   Nov 24 2008 16:17:31   Copyright (c) 1988-2005 Microsoft Corporation  Enterprise Edition (64-bit) on Windows NT 5.2 (Build 3790: Service Pack 2)

    Error Msg >> " An error occurred while executing  batch. error message is :There is not enough space on the disk"

    I just ran from Microsoft SQL Server Management Studio-> Query window

    select * from <View_XYZ>

    and this view is supposed to fetch 2311793 rows
    SD
    Wednesday, December 2, 2009 11:15 AM
  • I am not directly login to the box where the sql server hosted. we are working from Citrix client.
    SD
    Wednesday, December 2, 2009 11:34 AM
  • on Client workstation .. in C:\ there are almost 8-9 GB space availabe... And one more thing to share with you. The same view was running properly in sql server 2000.
    SD
    Wednesday, December 2, 2009 12:30 PM
  • SSMS is a .NET application, and may be attempting to spool the resultset onto a local drive resource.

    I would check the size of the disk where the page file is located and verify that there is enough free space.

    Or change the local page file to another disk where there is enough space.


    "You cannot do a kindness too soon because you never know how soon it will be too late." -Ralph Waldo Emerson
    Wednesday, December 2, 2009 2:28 PM
  • While executing the query I constantly checked the disk size of C:\ and M:\ (Where all data and log files pointed)
    But did not see any noticible changes in spaces..


    SD
    Thursday, December 3, 2009 6:48 AM
  • I am going to guess that the C:\ drive you mention is on the server, and that you are using SSMS on your local machine -NOT the server.

    Is that correct?
    "You cannot do a kindness too soon because you never know how soon it will be too late." -Ralph Waldo Emerson
    Thursday, December 3, 2009 6:53 AM
  • Ya.. But in my local machine there is enough space availabe.. almost 9 GB in c:\
    SD
    Friday, December 4, 2009 5:41 AM
  • Arnie & Goutam.

    The following from a blog (link at bottom) sounds similar:

    "When we run a query from SSMS and the results are presented in a grid (Query -> Results To -> Results To Grid), every row we receive from the server, we serialize it to a temporary file on disk, and when results have been received completely from the SQL Server instance, then SSMS maps those results to as many grids as resultsets were received. Eventually, you could receive an error like “An error occurred while executing batch. Error message is: There is not enough space on the disk”, if the disk where the file was located runs out of space in the middle of the query execution.

    To avoid the problem, either make some room so that the whole resultset fits in the disk pointed by the buffer returned by GetTempPath, or change your environment variable TMP to point to a volume where you have plenty of room. After changing the environment variable, you have to restart SSMS so that the change takes effect. "

    TMP/TEMP/USERPROFILE environment variables: the closer they point to, the better
    http://blogs.msdn.com/ialonso/archive/2008/05/29/tmp-temp-userprofile-environment-variables-the-closer-they-point-to-the-better.aspx


    Kalman Toth SQL SERVER & BI TRAINING
    New Book: Beginner Database Design & SQL Programming Using Microsoft SQL Server 2016

    • Edited by Kalman Toth Tuesday, November 14, 2017 7:09 PM
    Friday, December 4, 2009 7:29 AM